
package com.csc440.team2.database;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import com.csc440.team2.database.entities.FolderType;
import com.csc440.team2.database.entities.Message;
import com.csc440.team2.database.entities.Resource;
import com.csc440.team2.database.entities.ResourceType;


public class DBAdapter
{

    public static final String KEY_MESSAGE_ID = "id";
    public static final String KEY_MESSAGE_FOLDER_ID = "folder_id";
    public static final String KEY_MESSAGE_MESSAGE = "message";
    public static final String KEY_MESSAGE_READ_FLAG = "read_flag";
    public static final String KEY_MESSAGE_SENDER = "sender";

    public static final String KEY_RESOURCE_ID = "id";
    public static final String KEY_RESOURCE_MESSAGE_ID = "message_id";
    public static final String KEY_RESOURCE_TYPE_ID = "resource_type_id";
    public static final String KEY_RESOURCE_LOCATION = "location";

    private static final String TAG = "DBAdapter";

    private static final String DATABASE_NAME = "smil_messages";
    private static final String MESSAGE_TABLE = "messages";
    private static final String RESOURCE_TABLE = "resources";
    private static final int DATABASE_VERSION = 1;
    //
    // private static final String DATABASE_CREATE =
    // "create table titles (_id integer primary key autoincrement, "
    // + "isbn text not null, title text not null, "
    // + "publisher text not null);";

    private static final String MESSAGE_CREATE = "CREATE TABLE messages (" + "id INTEGER PRIMARY KEY AUTOINCREMENT"
            + ", message TEXT NOT NULL" + ", folder_id INTEGER NOT NULL" + ", read_flag INTEGER NOT NULL"
            + ", sender VARCHAR(50) NOT NULL" + ");";
    private static final String RESPIRCE_CREATE = "CREATE TABLE resources(" + "id INTEGER PRIMARY KEY AUTOINCREMENT"
            + ", message_id INTEGER NOT NULL" + ", resource_type_id INTEGER NOT NULL" + ", location VARCHAR(500)"
            + ");";

    private final Context context;

    private DatabaseHelper DBHelper;
    private SQLiteDatabase db;


    public DBAdapter(Context ctx)
    {
        this.context = ctx;
        DBHelper = new DatabaseHelper(context);
    }


    // ---opens the database---
    public DBAdapter open() throws SQLException
    {
        db = DBHelper.getWritableDatabase();
        return this;
    }


    // ---closes the database---
    public void close()
    {
        DBHelper.close();
    }


    // ---insert a title into the database---
    public boolean saveMessage(Message message)
    {
        boolean result;
        int rowsAffected = 0;
        ContentValues values = new ContentValues();
        values.put(KEY_MESSAGE_MESSAGE, message.getMessage());
        values.put(KEY_MESSAGE_FOLDER_ID, message.getFolder().ordinal() + 1);
        values.put(KEY_MESSAGE_READ_FLAG, message.isRead() == true ? 1 : 0);
        values.put(KEY_MESSAGE_SENDER, message.getSender());
        if (message.getId() == -1)
        {
            message.setId(db.insert(MESSAGE_TABLE, null, values));
            result = message.getId() != -1;
        }
        else
        {
            values.put(KEY_MESSAGE_ID, message.getId());
            rowsAffected = db.update(MESSAGE_TABLE, values, KEY_MESSAGE_ID + "=" + message.getId(), null);
            result = rowsAffected > 0;
        }
        return result;
    }


    // ---insert a title into the database---
    public boolean saveResource(Resource resource)
    {
        boolean result;
        int rowsAffected = 0;
        ContentValues values = new ContentValues();
        values.put(KEY_RESOURCE_MESSAGE_ID, resource.getMessageId());
        values.put(KEY_RESOURCE_TYPE_ID, resource.getResourceType().ordinal() + 1);
        values.put(KEY_RESOURCE_LOCATION, resource.getLocation());
        if (resource.getId() == -1)
        {
            resource.setId(db.insert(RESOURCE_TABLE, null, values));
            result = resource.getId() != -1;
        }
        else
        {
            values.put(KEY_RESOURCE_ID, resource.getId());
            rowsAffected = db.update(RESOURCE_TABLE, values, KEY_RESOURCE_ID + "=" + resource.getId(), null);
            result = rowsAffected > 0;
        }
        return result;
    }


    public boolean deleteMessage(Message message)
    {
        boolean result = false;

        deleteResourcesForMessage(message);

        if (db.delete(MESSAGE_TABLE, KEY_MESSAGE_ID + "=" + message.getId(), null) > 0)
        {
            message.setId(-1);
            result = true;
        }
        return result;
    }


    public boolean deleteResource(Resource resource)
    {
        boolean result = false;
        if (db.delete(RESOURCE_TABLE, KEY_RESOURCE_ID + "=" + resource.getId(), null) > 0)
        {
            resource.setId(-1);
            result = true;
        }
        return result;
    }


    public boolean deleteResourcesForMessage(Message message)
    {
        boolean result = false;

        if (db.delete(RESOURCE_TABLE, KEY_RESOURCE_MESSAGE_ID + "=" + message.getId(), null) > 0)
        {
            result = true;
        }

        return result;
    }


    public List<Message> getAllMessages()
    {
        List<Message> messages = new ArrayList<Message>();
        Cursor messageCursor;

        messageCursor = db.query(MESSAGE_TABLE, new String[] {
                KEY_MESSAGE_ID, KEY_MESSAGE_MESSAGE, KEY_MESSAGE_FOLDER_ID, KEY_MESSAGE_READ_FLAG, KEY_MESSAGE_SENDER
        }, null, null, null, null, null);

        while (messageCursor.moveToNext())
        {
            messages.add(fillMessageFromCursor(messageCursor));
        }

        return messages;
    }


    public List<Resource> getAllResources()
    {
        List<Resource> resources = new ArrayList<Resource>();
        Cursor resourceCursor;

        resourceCursor = db.query(RESOURCE_TABLE, new String[] {
                KEY_RESOURCE_ID, KEY_RESOURCE_MESSAGE_ID, KEY_RESOURCE_TYPE_ID, KEY_RESOURCE_LOCATION
        }, null, null, null, null, null);

        resources = fillResourceListFromCursor(resourceCursor);

        return resources;
    }


    public List<Resource> getResourcesByMessageId(long messageID)
    {
        List<Resource> resources = new ArrayList<Resource>();
        Cursor resourceCursor;
        Resource resource;

        resourceCursor = db.query(RESOURCE_TABLE, new String[] {
                KEY_RESOURCE_ID, KEY_RESOURCE_MESSAGE_ID, KEY_RESOURCE_TYPE_ID, KEY_RESOURCE_LOCATION
        }, KEY_RESOURCE_MESSAGE_ID + "=" + messageID, null, null, null, null);

        resources = fillResourceListFromCursor(resourceCursor);

        return resources;
    }


    private List<Resource> fillResourceListFromCursor(Cursor resourceCursor)
    {
        List<Resource> resources = new ArrayList<Resource>();

        while (resourceCursor.moveToNext())
        {
            resources.add(fillResourceFromCursor(resourceCursor));
        }

        return resources;
    }


    private Resource fillResourceFromCursor(Cursor resourceCursor)
    {
        return fillResourceFromCursor(resourceCursor, new Resource(resourceCursor.getLong(0)));
    }


    private Message fillMessageFromCursor(Cursor messageCursor)
    {
        return fillMessageFromCursor(messageCursor, new Message(messageCursor.getLong(0)));
    }


    private Resource fillResourceFromCursor(Cursor resourceCursor, Resource resource)
    {
        resource.setMessageId(resourceCursor.getLong(1));
        resource.setResourceType(ResourceType.values()[resourceCursor.getInt(2) - 1]);
        resource.setLocation(resourceCursor.getString(3));

        return resource;
    }


    private Message fillMessageFromCursor(Cursor messageCursor, Message message)
    {
        message.setMessage(messageCursor.getString(1));
        message.setFolder(FolderType.values()[messageCursor.getInt(2) - 1]);
        message.setRead(messageCursor.getInt(3) != 0);
        message.setSender(messageCursor.getString(4));

        return message;
    }


    public boolean getMessage(Message message) throws SQLException
    {
        boolean result = false;
        Cursor messageCursor = db.query(true, MESSAGE_TABLE, new String[] {
                KEY_MESSAGE_ID, KEY_MESSAGE_MESSAGE, KEY_MESSAGE_FOLDER_ID, KEY_MESSAGE_READ_FLAG, KEY_MESSAGE_SENDER
        }, KEY_MESSAGE_ID + "=" + message.getId(), null, null, null, null, null);
        if (messageCursor != null && messageCursor.moveToFirst())
        {
            fillMessageFromCursor(messageCursor, message);
            result = true;
        }
        return result;
    }


    public boolean getResource(Resource resource) throws SQLException
    {
        boolean result = false;
        Cursor resourceCursor = db.query(true, RESOURCE_TABLE, new String[] {
                KEY_RESOURCE_ID, KEY_RESOURCE_MESSAGE_ID, KEY_RESOURCE_TYPE_ID, KEY_RESOURCE_LOCATION
        }, KEY_RESOURCE_ID + "=" + resource.getId(), null, null, null, null, null);
        if (resourceCursor != null && resourceCursor.moveToFirst())
        {
            fillResourceFromCursor(resourceCursor, resource);
            result = true;
        }
        return result;
    }

    private static class DatabaseHelper extends SQLiteOpenHelper
    {
        DatabaseHelper(Context context)
        {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }


        @Override
        public void onCreate(SQLiteDatabase db)
        {
            Log.d(TAG, "Creating Database");
            db.execSQL(MESSAGE_CREATE);
            db.execSQL(RESPIRCE_CREATE);
        }


        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
        {
            Log.d(TAG, "Upgrading database from version " + oldVersion + " to " + newVersion
                    + ", which will destroy all old data");
            db.execSQL("DROP TABLE IF EXISTS messages");
            db.execSQL("DROP TABLE IF EXISTS resources");
            onCreate(db);
        }
    }
}
